package com.compty.paint.service.impl;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.compty.core.page.Page;
import com.compty.paint.domain.CatalogType;
import com.compty.paint.domain.ImageInfo;
import com.compty.paint.domain.Painter;
import com.compty.paint.domain.Painting;
import com.compty.paint.domain.PhotoData;

public class PaintingService{
	
	public Page pagedQuery(int curPage, int pageSize,Integer type){
		try {
			Connection conn = DBUtil.getConnection();
			Object result = getRows(conn,curPage,pageSize,type);
			int count = getCount(conn,type);
			Page page = new Page();
			page.setTotalCount(count);
			page.setPageNo(curPage);
			page.setPageSize(pageSize);
			page.setResult(result);
			
			conn.close();
			
			return page;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public int getCount(Connection conn,Integer type){
		String sql = "select count(*) as rowsCount from  t_painting ";
		if(type != null){
			 sql = "select count(*) as rowsCount from  t_painting where catalogtype_id= "+type;
		}
		try {
			PreparedStatement statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			while(rs.next()){
				int count = rs.getInt("rowsCount");
				return count;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	
	public List<Painting> getRows(Connection conn,int curPage, int pageSize,Integer type){
		List<Painting>  returnList = new ArrayList<Painting>();
		String sql = "select * from t_painting limit "+(curPage-1)*pageSize+","+pageSize;
		if(type != null ){
			sql = "select * from t_painting catalogtype_id= "+type+" limit "+(curPage-1)*pageSize+","+pageSize;
		}
		try {
			PreparedStatement statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			while(rs.next()){
				Painting painting = new Painting();
				
				Long catalogType = rs.getLong("catalogtype_id");
				if( catalogType >0 ){
					CatalogType catalogType2 = new CatalogType();
					catalogType2.setId(catalogType);
					painting.setCatalogType(catalogType2);
				}
				
				Long  painterid = rs.getLong("painterid");
				painting.setPainter(findPainterById(conn,painterid));
				
				Long image_id = rs.getLong("image_id");
				painting.setImageInfo(findImageinfoById(conn,image_id));
				Long zhengshu_imageid = rs.getLong("zhengshu_imageid");
				painting.setZhengshu(findImageinfoById(conn,zhengshu_imageid));
				
				returnList.add(painting);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return returnList;
	}
	
	public ImageInfo findImageinfoById(Connection conn,Long id){
		String sql = "select * from t_imageinfo where id=? ";
		try {
			PreparedStatement imagestatement = conn.prepareStatement(sql);
			imagestatement.setLong(1, id);
			ResultSet rs = imagestatement.executeQuery();
			while(rs.next()){
				ImageInfo imageInfo = new ImageInfo();
				imageInfo.setId(rs.getLong("id"));
				imageInfo.setOriginalName(rs.getString("originalName"));
				
				Long photo_data_id = rs.getLong("photo_data_id");
				PhotoData photoData = findPhotoDataById(conn, photo_data_id);
				imageInfo.setPhoto(photoData);
				
				imageInfo.setSharpId(rs.getLong("sharp_id"));
				imageInfo.setSuffix(rs.getString("suffix"));
				imageInfo.setNewNameUploaded(rs.getString("newname_uploaded"));
				imageInfo.setMemo(rs.getString("memo"));
				return imageInfo;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public PhotoData findPhotoDataById(Connection conn,Long id){
		String sql = "select * from t_imageinfo_data where id=?";
		try {
			PreparedStatement photostatement = conn.prepareStatement(sql);
			photostatement.setLong(1, id);
			ResultSet rs = photostatement.executeQuery();
			while(rs.next()){
				PhotoData photoData = new PhotoData();
				photoData.setId(rs.getLong("id"));
				java.sql.Blob blob = rs.getBlob("data");  
			    InputStream ins = blob.getBinaryStream(); 
			    ByteArrayOutputStream baos = new ByteArrayOutputStream();
		        byte[] b = new byte[1024];  
		        int len = 0;  
		        while ( (len = ins.read(b)) != -1) {  
		        	baos.write(b, 0, len);  
		        }  
				photoData.setData(baos.toByteArray());
				baos.flush();
				baos.close();  
			    ins.close();
				return photoData;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public Painter findPainterById(Connection conn,Long id){
		
		String sql = "select * from t_painter where id = ?";
		try {
			PreparedStatement painterStatement = conn.prepareStatement(sql);
			painterStatement.setLong(1, id);
			ResultSet rs = painterStatement.executeQuery();
			while(rs.next()){
				Painter painter = new Painter();
				String type = rs.getString("type");
				painter.setType(type);
				painter.setId(id);
				return painter;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return null;
	}

	/**
	 * 由画家ID查找其所有作品
	 * @param painterId 画家 ID
	 */
	public List<Painting>  findPaintingsByPainterId(Long painterId) {
		Connection conn = DBUtil.getConnection();
		List<Painting>  returnList = new ArrayList<Painting>();
		String sql = "select * from t_painting order by id desc";
		try {
			PreparedStatement statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			while(rs.next()){
				Painting painting = new Painting();
				
				Long catalogType = rs.getLong("catalogtype_id");
				if( catalogType >0 ){
					CatalogType catalogType2 = new CatalogType();
					catalogType2.setId(catalogType);
					painting.setCatalogType(catalogType2);
				}
				
				Long  painterid = rs.getLong("painterid");
				painting.setPainter(findPainterById(conn,painterid));
				
				Long image_id = rs.getLong("image_id");
				painting.setImageInfo(findImageinfoById(conn,image_id));
				Long zhengshu_imageid = rs.getLong("zhengshu_imageid");
				painting.setZhengshu(findImageinfoById(conn,zhengshu_imageid));
				
				returnList.add(painting);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if( conn != null ){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return returnList;
	}

}
